/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBUtil;

/**
 *
 * @author lab
 */
public class UserDAO {

    String SQL_CREATE_ACCOUNT = "INSERT INTO [User]( username, password, roleNo, status) VALUES(?, ?, ?, ?)";
    String SQL_READ_ACCOUNT = "SELECT * FROM [User]";
    String SQL_UPDATE_ACCOUNT = "UPDATE [User] SET password = ?,roleNo = ?, status = ? WHERE username = ? ";
    String SQl_DELETE_ACCOUNT = "DELETE FROM [User] WHERE username = ?";
    String SQL_GET_ACCOUNT_BY_USERNAME = "SELECT * FROM [User] WHERE username = ?";
    String SQL_GET_LOGIN = "SELECT * FROM [User] WHERE username =? and password =? and Status =?";
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    public String mesage = "";

    public UserDAO() {
    }

    public boolean login(String username, String password) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_GET_LOGIN);
            pst.setString(1, username);
            pst.setString(2, password);
            pst.setBoolean(3, true);
            rs = pst.executeQuery();
            if (rs.next()) {
                result = true;
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return result;
    }

    public boolean createAccount(User user) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_CREATE_ACCOUNT);
            pst.setString(1, user.getUsername());
            pst.setString(2, user.getPassword());
            pst.setInt(3, user.getRoleNo());
            pst.setBoolean(4, user.isStatus());
            if (pst.executeUpdate() > 0) {
                mesage = "Create account successfully!";
                result = true;
            } else {
                mesage = "Create account unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            mesage = "Create account failure!";
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public ArrayList<User> getAllUser() {
        ArrayList<User> users = new ArrayList<User>();
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_ACCOUNT);
            rs = pst.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRoleNo(rs.getInt("roleNo"));
                user.setStatus(rs.getBoolean("status"));
                users.add(user);
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return users;
    }

    public boolean updateUser(User user) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_UPDATE_ACCOUNT);
            pst.setString(1, user.getPassword());
            pst.setInt(2, user.getRoleNo());
            pst.setBoolean(3, user.isStatus());
            pst.setString(4, user.getUsername());

            if (pst.executeUpdate() > 0) {
                mesage = "Update account successfully!";
                result = true;
            } else {
                mesage = "Update account unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            mesage = "Update failure!";
        } finally {

            DBUtil.closeConn(conn);
        }
        return result;
    }

    public boolean deleteUser(String username) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQl_DELETE_ACCOUNT);
            pst.setString(1, username);
            if (pst.executeUpdate() > 0) {
                mesage = "Delete successfully!";
                result = true;
            } else {
                mesage = "Delete unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            mesage = "Delete failure!";
        } finally {

            DBUtil.closeConn(conn);
        }
        return result;
    }

    public User getUserByUserName(String username) {
        User user = null;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_GET_ACCOUNT_BY_USERNAME);
            pst.setString(1, username);
            rs = pst.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRoleNo(rs.getInt("roleNo"));
                user.setStatus(rs.getBoolean("status"));
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return user;
    }
}
